CREATE TABLE [dbo].[ZPRODUCTS]
	(
[PRODUCT_ID] [VARCHAR](30),
[PRODUCT_NAME] [VARCHAR](50),
[CATEGORY_ID] [VARCHAR](50),
[CATEGORY_NAME] [VARCHAR](50),
[WEIGHT/CAPACITY] [VARCHAR](30),
[PRICE] [INT],
[FilePath] [VARCHAR](300),

CONSTRAINT PK1 PRIMARY KEY([PRODUCT_ID])
);

SELECT * FROM  [dbo].[ZPRODUCTS]
DROP TABLE  [dbo].[ZPRODUCTS]

------------------------------------------------------------------------------------------------------
CREATE TABLE ZCOMMENT
		(
PRODUCT_ID VARCHAR(30),
COMMENTID INT,
COMMENTERNAME VARCHAR(100),
COMMENTDES VARCHAR(100),

CONSTRAINT PK2 PRIMARY KEY (COMMENTID,COMMENTERNAME),
CONSTRAINT FK1 FOREIGN KEY (PRODUCT_ID)REFERENCES ZPRODUCTS(PRODUCT_ID)
);

SELECT * FROM  ZCOMMENT
DROP TABLE  ZCOMMENT

------------------------------------------------------------------------------------------------------


CREATE TABLE ZFEEDBACK
		(
FNAME VARCHAR(100),
ADDRESS VARCHAR(100),
FEEDBACK VARCHAR(1000),
);

SELECT * FROM  ZFEEDBACK
DROP TABLE  ZFEEDBACK

------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------

CREATE TABLE ZUSERACCOUNT
		(
AUSERID INT,
USERNAME VARCHAR(100),
PASSWORD VARCHAR(100),
REGISTERDATE DATETIME DEFAULT GETDATE(),
LAST_PURCHASE INT DEFAULT 0,
 
CONSTRAINT PK4 PRIMARY KEY (USERNAME),
CONSTRAINT FK2 FOREIGN KEY (AUSERID)REFERENCES ZUSER(USERID)
);

SELECT * FROM ZUSERACCOUNT
DROP TABLE ZUSERACCOUNT

------------------------------------------------------------------------------------------------------

CREATE TABLE ZSHOPPING_CART 
		(
CART_ID INT,
PRODUCT_ID VARCHAR(30),
QTY INT DEFAULT 1,

CONSTRAINT PK5 PRIMARY KEY (CART_ID,PRODUCT_ID),
CONSTRAINT FK3 FOREIGN KEY (PRODUCT_ID)REFERENCES ZPRODUCTS(PRODUCT_ID));

SELECT * FROM  ZSHOPPING_CART
DROP TABLE ZSHOPPING_CART



SELECT * FROM  [DBO].[ZPRODUCTS]
SELECT * FROM  ZCOMMENT
SELECT * FROM ZUSER
SELECT * FROM ZUSERACCOUNT
SELECT * FROM  ZSHOPPING_CART
SELECT * FROM  ZFEEDBACK



DROP TABLE  [DBO].[ZPRODUCTS]
DROP TABLE  ZCOMMENT
DROP TABLE ZUSER
DROP TABLE ZSHOPPING_CART
DROP TABLE  ZFEEDBACK
DROP TABLE ZUSERACCOUNT



--CREATE TABLE [DBO].[ZPRODUCTS]
--	(
--[PRODUCT_ID] [VARCHAR](30),
--[PRODUCT_NAME] [VARCHAR](50),
--[CATEGORY_ID] [VARCHAR](50),
--[CATEGORY_NAME] [VARCHAR](50),
--[WEIGHT/CAPACITY] [VARCHAR](30),
--[PRICE] [FLOAT],
--[FILEPATH] [VARCHAR](300),
--
--CONSTRAINT PK1 PRIMARY KEY([PRODUCT_ID])
--
--);



INSERT INTO dbo.ZPRODUCTS VALUES('VEG1','CARROT','1','VEGITABLE','500g',30,'images\CARROT.JPG')
INSERT INTO dbo.ZPRODUCTS VALUES('VEG2','BATANA','1','VEGITABLE','500g',35,'images\BATANA.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG3','BEATROOT','1','VEGITABLE','500g',40,'images\BEATROOT.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG4','BELL_PEPPER','1','VEGITABLE','500g',28,'images\BELL_PEPPER.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG5','BELLPEPPER_GREEN','1','VEGITABLE','500g',30,'images\BELLPEPPER_GREEN.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG6','BRINJALS','1','VEGITABLE','500g',32,'images\BRINJALS.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG7','COLIFLOWER','1','VEGITABLE','500g',50,'images\COLIFLOWER.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG8','CUECUMBER','1','VEGITABLE','500g',27,'images\CUECUMBER.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG9','GINGER','1','VEGITABLE','100g',15,'images\GINGER.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG10','GOWA','1','VEGITABLE','500g',21,'images\GOWA.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG11','GOWA_LEAF','1','VEGITABLE','500g',18,'images\GOWA_LEAF.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG12','LADIES_FINGERS','1','VEGITABLE','500g',30,'images\LADIES_FINGERS.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG13','MASHROOM','1','VEGITABLE','250g',40,'images\MASHROOM.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG14','ONION','1','VEGITABLE','500g',45,'images\ONION.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG15','POTATO','1','VEGITABLE','500g',55,'images\POTATO.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG16','SALAD_LEAF','1','VEGITABLE','250g',12,'images\SALAD_LEAF.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('VEG17','SUDU_LUNA','1','VEGITABLE','100g',20,'images\SUDU_LUNA.JPG')
INSERT INTO dbo.ZPRODUCTS VALUES('VEG18','TOMATO','1','VEGITABLE','250g',30,'images\TOMATO.JPG') 

INSERT INTO dbo.ZPRODUCTS VALUES('FRT1','APPLE','2','FRUIT','250g',100,'images\APPLE.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('FRT2','BANANA(AMBUL)','2','FRUIT','250g',60,'images\BANANA(AMBUL).JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('FRT3','GRAPES','2','FRUIT','100g',150,'images\GRAPES.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('FRT4','MANGO','2','FRUIT','250g',70,'images\MANGO.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('FRT5','ORANGES','2','FRUIT','250g',90,'images\ORANGES.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('FRT6','PAPAYA','2','FRUIT','500g',30,'images\PAPAYA.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('FRT7','PIEAPPLE','2','FRUIT','250g',70,'images\PIEAPPLE.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('FRT8','STRAWBERY','2','FRUIT','250g',250,'images\STRAWBERY.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('FRT9','WATER_MELLON','2','FRUIT','250g',70,'images\WATER_MELLON.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('FRT10','WOOD-APPLES','2','FRUIT','250g',30,'images\WOOD-APPLES.JPG') 

INSERT INTO dbo.ZPRODUCTS VALUES('SWT1','CAKE','3','SWEETS','1Kg',1000,'images\CAKE.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('SWT2','CHO-2','3','SWEETS','100g',120,'images\CHO-2.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('SWT3','CHOCALATES','3','SWEETS','60g',135,'images\CHOCALATES.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('SWT4','CREAM_CRACKER','3','SWEETS','250g',35,'images\CREAM_CRACKER.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('SWT5','KIT-KAT','3','SWEETS','100g',80,'images\KIT-KAT.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('SWT6','MILK-BUSCUIT','3','SWEETS','178g',63,'images\MILK-BUSCUIT.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('SWT7','TOBLERONE','3','SWEETS','80g',220,'images\TOBLERONE.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('SWT8','TOFFEE','3','SWEETS','100',180,'images\TOFFEE.JPG') 


INSERT INTO dbo.ZPRODUCTS VALUES('GRO1','CELONTA_TEA','4','GROSARY','500g',255,'images\CELONTA_TEA.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('GRO2','COCONUT_POWDER','4','GROSARY','500g',200,'images\COCONUT_POWDER.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('GRO3','HORLICKS','4','GROSARY','800g',390,'images\HORLICKS.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('GRO4','JAM','4','GROSARY','235g',135,'images\JAM.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('GRO5','NESCAFE_COFFEE','4','GROSARY','50g',255,'images\NESCAFE_COFFEE.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('GRO6','NOODLES','4','GROSARY','250',175,'images\NOODLES.JPG') 
INSERT INTO dbo.ZPRODUCTS VALUES('GRO7','SUGURE','4','GROSARY','1kg',100,'images\SUGURE.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('GRO8','VEGETABLE_OIL','4','GROSARY','1L',620,'images\VEGETABLE_OIL.JPG')   


INSERT INTO dbo.ZPRODUCTS VALUES('FROZ1','BEEF_SAUSAGES','5','FROZEN','200g',120,'images\BEEF_SAUSAGES.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('FROZ2','CHICKEN-SAUSAGES','5','FROZEN','200g',140,'images\CHICKEN-SAUSAGES.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('FROZ3','CHINESS_ROLES','5','FROZEN','500g',220,'images\CHINESS_ROLES.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('FROZ4','CHOCALATE_ICECREAM','5','FROZEN','1L',175,'images\CHOCALATE_ICECREAM.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('FROZ5','FRUIT_AND_NUT_ICECREAM','5','FROZEN','1L',200,'images\FRUIT_AND_NUT_ICECREAM.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('FROZ6','ICECREAM_CUP','5','FROZEN','18g',35,'images\ICECREAM_CUP.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('FROZ7','KEELS_SAUSAGES','5','FROZEN','200g',135,'images\KEELS_SAUSAGES.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('FROZ8','MUTTON-SAUSAGES','5','FROZEN','200g',155,'images\MUTTON-SAUSAGES.JPG')   


INSERT INTO dbo.ZPRODUCTS VALUES('CHIL1','ANCHOR_YOGHURT','6','CHILLED','18g',25,'images\ANCHOR_YOGHURT.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('CHIL2','LUCKY_YOGHURT','6','CHILLED','18g',22,'images\LUCKY_YOGHURT.JPG')   
--INSERT INTO dbo.ZPRODUCTS VALUES('CHIL3','       ','6','CHILLED','200g',155,'images\   ')   
--INSERT INTO dbo.ZPRODUCTS VALUES('CHIL4','       ','6','CHILLED','200g',155,'images\   ')   
--INSERT INTO dbo.ZPRODUCTS VALUES('CHIL5','       ','6','CHILLED','200g',155,'images\   ')   
--INSERT INTO dbo.ZPRODUCTS VALUES('CHIL6','       ','6','CHILLED','200g',155,'images\   ')   
--

INSERT INTO dbo.ZPRODUCTS VALUES('BEV1','7UP','7','BEVERAGES','1.5L',145,'images\7UP.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('BEV2','COCA_COLA','7','BEVERAGES','1.5L',145,'images\COCA_COLA.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('BEV3','CREAM_SODA','7','BEVERAGES','1.5Lg',135,'images\CREAM_SODA.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('BEV4','FANTA','7','BEVERAGES','1.5L',130,'images\FANTA.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('BEV5','MILO','7','BEVERAGES','200ML',30,'images\MILO.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('BEV6','MINARAL','7','BEVERAGES','IL',36,'images\MINARAL.JPG')   


INSERT INTO dbo.ZPRODUCTS VALUES('CAN1','COCONUT-CAN','8','CANFOODS','400ml',145,'images\COCONUT-CAN.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('CAN2','JACK_MAKERAL','8','CANFOODS','400ml',250,'images\JACK_MAKERAL.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('CAN3','CAN_MEAT','8','CANFOODS','400ml',450,'images\CAN_MEAT.JPG')   
--INSERT INTO dbo.ZPRODUCTS VALUES('CAN4','         ','8','CANFOODS','1.5L',145,'images\   ')   
--INSERT INTO dbo.ZPRODUCTS VALUES('CAN5','         ','8','CANFOODS','1.5L',145,'images\   ')   



INSERT INTO dbo.ZPRODUCTS VALUES('FIH1','HURULLA','9','FISH','1.5L',145,'images\HURULLA.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('FIH3','KELAWALLA','9','FISH','1.5L',145,'images\KELAWALLA.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('FIH2','SALAYA','9','FISH','1.5L',145,'images\SALAYA.JPG')   
--INSERT INTO dbo.ZPRODUCTS VALUES('FIH3','          ','9','FISH','1.5L',145,'images\   ')   
--INSERT INTO dbo.ZPRODUCTS VALUES('FIH4','          ','9','FISH','1.5L',145,'images\   ')   
--INSERT INTO dbo.ZPRODUCTS VALUES('FIH5','          ','9','FISH','1.5L',145,'images\   ')   


INSERT INTO dbo.ZPRODUCTS VALUES('MET1','CHICKEN','10','MEAT','1Kg',390.50,'images\CHICKEN.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('MET2','EGGS','10','MEAT','400ml',250.00,'images\EGGS.JPG')   
--INSERT INTO dbo.ZPRODUCTS VALUES('MET4','          ','10','MEAT','1.5L',145,'images\   ')   
--INSERT INTO dbo.ZPRODUCTS VALUES('MET5','          ','10','MEAT','1.5L',145,'images\   ')   
--

INSERT INTO dbo.ZPRODUCTS VALUES('BAK1','BREAD','11','BAKERY','400g',50,'images\BREAD.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('BAK2','FISH_BUN','11','BAKERY','35g',25,'images\FISH_BUN.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('BAK3','HOTDOG','11','BAKERY','40g',30,'images\HOTDOG.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('BAK4','SAUSAGE_BUNNS','11','BAKERY','1.5L',145,'images\SAUSAGE_BUNNS.JPG')   
--INSERT INTO dbo.ZPRODUCTS VALUES('BAK5','          ','11','BAKERY','1.5L',145,'images\   ')   

INSERT INTO dbo.ZPRODUCTS VALUES('LIQ1','CARLSBERG_BEER','12','LIQUER','1.5L',1500,'images\CARLSBERG_BEER.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('LIQ2','KAPACHI_BEER','12','LIQUER','1.5L',1200,'images\KAPACHI_BEER.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('LIQ3','LION_BEER','12','LIQUER','1.5L',1000,'images\LION_BEER.JPG')   
INSERT INTO dbo.ZPRODUCTS VALUES('LIQ4','REDHORSE_BEER','12','LIQUER','1.5L',145,'images\REDHORSE_BEER   ')   
--INSERT INTO dbo.ZPRODUCTS VALUES('LIQ5',' 7UP         ','12','LIQUER','1.5L',145,'   ')   
--INSERT INTO dbo.ZPRODUCTS VALUES('LIQ6',' 7UP         ','12','LIQUER','1.5L',145,'   ')   







